package com.cn.ccp.service.course;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.action.BaseAction;
import com.cn.ccp.bean.CourseBean;
import com.cn.ccp.bean.CourseContentBean;
import com.cn.ccp.bean.CourseSettingBean;
import com.cn.ccp.bean.SelectedCourse;
import com.cn.ccp.bean.SelectedCourseBean;
import com.cn.ccp.bean.SpecialtyBean;
import com.cn.ccp.bean.StudentMarkedHistoryBean;
import com.cn.ccp.bean.UserBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;
import com.cn.ccp.util.Contants;

/**
 * 学生选课service
 * @author renyongcheng
 *
 */
public class SelectCourseService extends BasicService{
	private Logger log = Logger.getLogger(SelectCourseService.class);
	/**
	 * 需要审批的选课记录
	 * @param specialtyId:专业id
	 * @return
	 */
	public int countSelectApprove(String specialtyId){
		String sql = "SELECT count(1) FROM selected_course where specialty_id = ? and status = ?";
		return jdbcTemplate.queryForInt(sql,new Object[]{specialtyId,Contants.SELECTED_COURSE_STATUS_WAITING_APPROVE});
	}
	
	/**
	 * 本专业需要审批的学生选课数据
	 * @param specialtyId
	 * @param start
	 * @param pagesize
	 * @return
	 */
	public List<SelectedCourseBean> pageQueryForReleaseApproval(String specialtyId,int start,int pagesize){
		String sql = "SELECT * FROM selected_course  where specialty_id=? and status = ? LIMIT ?,?";
		return jdbcTemplate.query(sql, new Object[]{specialtyId,Contants.SELECTED_COURSE_STATUS_WAITING_APPROVE,start,pagesize}, new SelectCourseMapper());
	}
	
	/**
	 * 根据选课id查询选课内容
	 * @param selectedCourseId
	 * @return
	 */
	public Object getSelectedCourseBySelectedId(String selectedCourseId){
		String sql = "SELECT * FROM selected_course  where id=? ";
		return jdbcTemplate.queryForObject(sql, new Object[]{selectedCourseId}, new SelectCourseMapper());
	}
	
	/**
	 * 查询可以选课的课程总数
	 * @param courseName：课程名称
	 * @param specialtyId：专业id
	 * @param teacherID：老师id
	 * @param starLevel：课程星级
	 * @param year：发布年
	 * @param selectLanguageId : 课程语言类型 
	 * @return
	 */
	public int canSelectedCourse(String userId,String grade,String specialtyId,String facultyId,String courseName,String courseType,String teacherID,String starLevel,String year,String selectLanguageId){
		StringBuffer sql =  new StringBuffer();
		List<String> param = new ArrayList<String>();
		sql.append("SELECT count(1) FROM courses where status = 5");
		sql.append(" and id in (SELECT course_id FROM course_mapping WHERE  (course_mapping_type = 1 AND other_id = ?)");
		sql.append(" OR ");
		sql.append(" (course_mapping_type = 2 AND other_id = ?)");
		sql.append(" OR ");
		sql.append(" (course_mapping_type = 3 AND other_id = ?)");
		sql.append(" OR ");
		sql.append(" (course_mapping_type = 4 AND other_id = ?)");
		sql.append(" GROUP BY course_id) ");
		param.add(userId);
		param.add(grade);
		param.add(specialtyId);
		param.add(facultyId);
		if(courseName != null && !"".equalsIgnoreCase(courseName.trim())){
			sql.append(" and courses_name like ?");
			param.add("%"+courseName+"%");
		}
		if(courseType != null && !"".equalsIgnoreCase(courseType.trim())){
			sql.append(" and course_type = ? ");
			param.add(courseType);
		}
		if(teacherID != null && !"".equalsIgnoreCase(teacherID.trim())){
			sql.append(" and teacher_id = ?");
			param.add(teacherID);
		}
		if(starLevel != null && !"".equalsIgnoreCase(starLevel.trim())){
			sql.append(" and FLOOR(start_level) = ?");
			param.add(starLevel);
		}
		if(year != null && !"".equalsIgnoreCase(year.trim())){
			sql.append(" and year(online_begin_time) = ?");
			param.add(year);
		}
		if(selectLanguageId != null && !"".equalsIgnoreCase(selectLanguageId.trim())){
			sql.append(" and language_type = ?");
			param.add(selectLanguageId);
		}
		log.info(sql.toString());
		StringBuffer b = new StringBuffer();
		for(String a : param)
			b.append(a + ",");
			log.info(b.toString());
		if(param.size() == 0)
			return jdbcTemplate.queryForInt(sql.toString());
		else
			return jdbcTemplate.queryForInt(sql.toString(),param.toArray());
	}
	
	/**
	 * 
	 * @param start：开始页面
	 * @param pagesize：每页显示数量
	 * @param courseName：课程名称
	 * @param specialtyId：专业id
	 * @param teacherID：教师id
	 * @param starLevel：星级
	 * @param year：发布年份
	 * @param selectLanguage : 课程语言分类
	 * @return
	 */
	public List<CourseBean> canSelectedCourse(String userId,String grade,String specialtyId,String facultyId,int start,int pagesize,String courseName,String courseType,String teacherID,String starLevel,String year,String selectLanguageId){
		StringBuffer sql = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		sql.append("select c.file_path_id, c.id,c.courses_name,c.start_level,c.image , count(distinct h.id) as marks,count(distinct s.id) as studys ,c.teacher_id,u.username ,c.create_time, ");
		sql.append(" ( case when ss.student_id=? then 1 else 0 end) as studied ,c.originate");
		sql.append(" from courses as c   ");
		sql.append(" left outer join student_marked_history h on c.id = h.course_id ");
		sql.append(" left outer join selected_course s  on c.id = s.course_id and s.status = 1");
		sql.append(" left outer join selected_course ss  on c.id = ss.course_id and ss.status = 1 and ss.student_id=?");
		sql.append(" left outer join user u on u.id =  c.teacher_id  	");
		sql.append(" where c.status=5 ");
		sql.append(" and c.id in (SELECT cm.course_id FROM course_mapping cm WHERE  (cm.course_mapping_type = 1 AND cm.other_id = ?)");
		sql.append(" OR ");
		sql.append(" (cm.course_mapping_type = 2 AND cm.other_id = ?)");
		sql.append(" OR ");
		sql.append(" (cm.course_mapping_type = 3 AND cm.other_id = ?)");
		sql.append(" OR ");
		sql.append(" (cm.course_mapping_type = 4 AND cm.other_id = ?)");
		sql.append(" GROUP BY cm.course_id) ");
		param.add(userId);
		param.add(userId);
		param.add(userId);
		param.add(grade);
		param.add(specialtyId);
		param.add(facultyId);
		if(courseName != null && !"".equalsIgnoreCase(courseName.trim())){
			sql.append(" and c.courses_name like ?");
			param.add("%"+courseName+"%");
		}
		if(courseType != null && !"".equalsIgnoreCase(courseType.trim())){
			sql.append(" and c.course_type = ?");
			param.add(courseType);
		}
		if(teacherID != null && !"".equalsIgnoreCase(teacherID.trim())){
			sql.append(" and c.teacher_id = ?");
			param.add(teacherID);
		}
		if(starLevel != null && !"".equalsIgnoreCase(starLevel.trim())){
			sql.append(" and FLOOR(c.start_level) = ?");
			param.add(starLevel);
		}
		if(year != null && !"".equalsIgnoreCase(year.trim())){
			sql.append(" and year(c.online_begin_time) = ?");
			param.add(year);
		}
		if(selectLanguageId != null && !"".equalsIgnoreCase(selectLanguageId.trim())){
			sql.append(" and language_type = ?");
			param.add(selectLanguageId);
		}
		
		
		
		sql.append(" and ((NOW() >= c.online_begin_time AND (c.online_end_time is null or NOW() <= c.online_end_time) ) OR c.online_begin_time IS NULL) group by  c.id limit ?, ?");
		param.add( start);
		param.add( pagesize);
		log.info(sql.toString());
		log.info("---------------param.size() = "+param.size());
		StringBuffer b = new StringBuffer();
		for(Object a : param)
			b.append(a + ",");
			log.info(b.toString());
		return jdbcTemplate.query(sql.toString(), param.toArray(), new CourseMapper());
	}
	
	/**
	 * 选课时查询课程详细信息
	 */
	public CourseBean getCouseDetailById(String strCourseId){
		StringBuffer sql = new StringBuffer();
		sql.append("select c.file_path_id, tg.teaching_group_name, c.id,c.courses_name,c.start_level,c.image , count(distinct h.id) as marks,count(distinct s.id) as studys ,c.teacher_id,u.username ,ss.specialtyname,c.descript,u.descript as teacherDes,c.is_high_quality");
		sql.append(" from courses as c   ");
		sql.append(" LEFT OUTER JOIN teaching_group tg ON tg.id = c.teaching_group_id ");
		sql.append(" left outer join student_marked_history h on c.id = h.course_id ");
		sql.append(" left outer join selected_course s  on c.id = s.course_id and s.status = 1");
		sql.append(" left outer join user u on u.id =  c.teacher_id  	");
		sql.append(" left outer join specialty ss on ss.id = c.specialty_id ");
		sql.append(" where c.status=5 and c.id = ? ");
		log.info("------------strCourseId = "+strCourseId);
		log.info(sql.toString());
		return (CourseBean)jdbcTemplate.queryForObject(sql.toString(), new Object[]{strCourseId}, new CourseDetailMapper());
	}
	
	
	public int onlineCount(String userId){
//		String sql = "SELECT count(1) FROM selected_course where status = 1 and student_id = ?";
		//查询该学生选择的所有课程进行显示
		String sql = "SELECT count(1) FROM selected_course where student_id = ?";
		return jdbcTemplate.queryForInt(sql, new Object[]{userId});
	}
	
	
	public int markdCountByCourseId(String courseId){
		//查询该学生选择的所有课程进行显示
		String sql = "SELECT count(1) FROM student_marked_history where course_id = ?";
		return jdbcTemplate.queryForInt(sql, new Object[]{courseId});
	}
	
	
	public List<SelectedCourse> onlineList(String userId,int start,int pagesize){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" select c.file_path_id, sc.status, sc.has_marked, sc.id, sc.course_id,sc.courser_name,count(distinct h.id) as marks,u.username,c.start_level, c.image ,");
		sqlBuf.append(" count(distinct scd.course_detail_id)/count(distinct cdetail.id) as studiedRate");
		sqlBuf.append(" from selected_course sc ");
		sqlBuf.append(" left outer join  student_marked_history h on sc.course_id = h.course_id");
		sqlBuf.append(" left outer join  courses c on c.id = sc.course_id");
		sqlBuf.append(" left outer join  user u on u.id =  c.teacher_id ");
		sqlBuf.append(" left outer join  selected_course_detail scd on scd.selected_course_id = sc.id and scd.has_studied = true ");
		sqlBuf.append(" left outer join  courses_detail cdetail on cdetail.courses_id = sc.course_id ");
		sqlBuf.append(" where  sc.student_id = ? ");
		sqlBuf.append(" group by  sc.id ORDER BY sc.status ASC limit ?, ? ");
		return jdbcTemplate.query(sqlBuf.toString(), new Object[]{userId,start,pagesize}, new  OnlineCourseMapper());
	}
	
	
	public SelectedCourse getSelectedCourseByStuAndCourseId(String stuId,String courseId){
		String sql = "select sc.score from selected_course sc where sc.student_id=? and sc.course_id = ?";
		return (SelectedCourse)jdbcTemplate.queryForObject(sql, new Object[]{stuId,courseId}, new SelectedCourseMapper());
	}
	
	class SelectedCourseMapper implements RowMapper{

		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			SelectedCourse bean = new SelectedCourse();
			bean.setScore(rs.getInt("score"));
			return bean;
		}
		
	}
	/**
	 * 该用户当前需要考试的总的课程数
	 * @param userId
	 * @return
	 */
	public int onlineTestCount(String userId){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" select count(sc.course_id)  from selected_course sc ");
		sqlBuf.append(" inner JOIN course_setting cset on cset.course_id = sc.course_id ");
		sqlBuf.append(" and  cset.type=1  AND cset.active = 1 ");
		sqlBuf.append(" where sc.student_id=?  ");
		return jdbcTemplate.queryForInt(sqlBuf.toString(), new Object[]{userId});
	}
	
	public void setSelectedCourseFinished(String selectedCourseId){
		 jdbcTemplate.update("update selected_course set status =? where id = ? ", new Object[]{Contants.SELECTED_COURSE_STATUS_GRADUATED,selectedCourseId});
	}
	
	
	/**
	 * 当前需要考试的科目列表
	 * @param userId
	 * @param start
	 * @param pagesize
	 * @return
	 */
	public List<CourseSettingBean> onlineTestList(String userId,int start,int pagesize){
		StringBuffer sqlBuf = new StringBuffer();
		
		sqlBuf.append(" select sc.id as selectCourseId,sc.course_id,sc.courser_name,cset.*,csetr.score,csetr.id as recordid from selected_course sc ");
		sqlBuf.append(" inner JOIN course_setting cset on cset.course_id = sc.course_id ");
		sqlBuf.append(" 	and  cset.type=1   and cset.active = 1 ");
		sqlBuf.append(" left outer join course_setting_record csetr on csetr.setting_id = cset.id and csetr.user_id = ? ");
		sqlBuf.append(" where sc.student_id=? ");
		sqlBuf.append(" order by recordid LIMIT ?,?");
		log.info(sqlBuf.toString());
		log.info("--userId = "+userId);
		return jdbcTemplate.query(sqlBuf.toString(), new Object[]{userId,userId,start,pagesize}, new  CourseSettingMapper());
		
	}
	
	
	/**
	 * 根据类型查询数据
	 * @param settype
	 * @return
	 */
	public List<CourseSettingBean> getCourseSettingByType(String courseId, int settype,String userId){
		StringBuffer sqlBuf = new StringBuffer();
		
		sqlBuf.append(" select distinct sc.course_id,sc.courser_name,cset.*,csetr.score,csetr.id as recordid from selected_course sc ");
		sqlBuf.append(" LEFT OUTER JOIN course_setting cset on cset.course_id = sc.course_id ");
		if(settype == Contants.COURSE_SETTING_TYPE_TEST || settype == Contants.COURSE_SETTING_TYPE_INVEST )
			sqlBuf.append(" and  ( cset.active = 1 and cset.type= ?   ) ");
		else 
			sqlBuf.append(" and  cset.type = ?    ");
		sqlBuf.append(" left outer join course_setting_record csetr on csetr.setting_id = cset.id  and csetr.user_id = ? ");
		sqlBuf.append(" where cset.course_id=? ");
		sqlBuf.append(" order by recordid ");
		log.info(sqlBuf.toString());
		log.info(settype + " : "+courseId );
		return jdbcTemplate.query(sqlBuf.toString(), new Object[]{settype,userId,courseId}, new  CourseSettingMapper());
		
	}
	
	
	
	
	
	
	public List<SpecialtyBean> allSpecialty(){
		String sql = "SELECT id,specialtyname FROM specialty";
		return jdbcTemplate.query(sql,new SpecialtyMapper());
	}
	
	public List<UserBean> allTeachers(){
		String sql = "SELECT id,username FROM user where user_type in (2,3)";
		return jdbcTemplate.query(sql,new UserMapper());
	}
	
	public List<String> allReleaseTimes(){
		return jdbcTemplate.queryForList("SELECT distinct year(create_time) FROM eduonline.courses ",String.class);
	}
	
	public List<CourseContentBean> getCourseContentsDetailById(String courseId){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select cc.id,cc.chapter_number,cc.chapter_title,cc.section_number,cc.section_file_name,cc.section_file_type,cc.source_file_url,cc.section_file_url,cc.can_browse ");
		sqlBuf.append(" from courses_detail cc ");
		sqlBuf.append(" where courses_id = ? order by chapter_number,section_number");
		return jdbcTemplate.query(sqlBuf.toString(),new CourseContentDetailMapper());
	}
	
	public boolean checkISSelectCourse(String userid ,String courseid){
		String sql = "SELECT count(1) FROM selected_course WHERE student_id = ? AND course_id = ?";
		return jdbcTemplate.queryForInt(sql, new Object[]{userid ,courseid})>0 ;
	}
	
	 private void deleteOldData(String strCourseId, String userId){
		 String sql = "delete from selected_course_detail where selected_course_id = (SELECT id FROM selected_course WHERE student_id = ? AND course_id = ?)";
		 jdbcTemplate.update(sql, new Object[]{userId ,strCourseId});
		 sql = "delete  FROM selected_course WHERE student_id = ? AND course_id = ? ";
		 jdbcTemplate.update(sql, new Object[]{userId ,strCourseId});
	 }
	
	
	 /**
	  *  根据选课id取消该门课程
	  * @param selectCourseId
	  */
	 public boolean cancelCourse(final String selectCourseId,final String courseId){
		 return (Boolean)transactionTemplate.execute(new TransactionCallback() {
				@Override
				public Object doInTransaction(TransactionStatus ts) {
					try{
						 String sql = "delete from selected_course_detail where selected_course_id =  ?";
						 jdbcTemplate.update(sql, new Object[]{selectCourseId});
						 sql = "delete  FROM selected_course WHERE id = ?  ";
						 jdbcTemplate.update(sql, new Object[]{selectCourseId});
						 sql = "delete from course_setting_record where user_id = ? and setting_id in (SELECT id FROM course_setting WHERE course_id = ? )";
						 jdbcTemplate.update(sql, new Object[]{selectCourseId,courseId});
						return Boolean.TRUE;
					}catch(Exception e){
						log.error("add select course failed : ",e);
						ts.setRollbackOnly();  
						return Boolean.FALSE; 
					}
				}
			});
	 }
	 
	 
	
	public boolean doAddCourse(final String strCourseId,final String userId,final String userName){
		return (Boolean)transactionTemplate.execute(new TransactionCallback() {
			@Override
			public Object doInTransaction(TransactionStatus ts) {
				try{
					deleteOldData(strCourseId,userId);
					addNewSelectCourse(strCourseId,userId,userName);
					return Boolean.TRUE;
				}catch(Exception e){
					log.error("add select course failed : ",e);
					ts.setRollbackOnly();  
					return Boolean.FALSE; 
				}
			}
		});
	}
	
	
	private void addNewSelectCourse(String strCourseId,final String userId,final String userName){
		StringBuffer sql = new StringBuffer();
		sql.append("select c.id,c.courses_name,c.specialty_id,c.prices,c.credit ,ss.specialtyname ");
		sql.append(" from courses as c   ");
		sql.append(" left outer join user u on u.id =  c.teacher_id  	");
		sql.append(" left outer join specialty ss on ss.id = c.specialty_id ");
		sql.append(" where c.id = ? ");
		final CourseBean bean =  (CourseBean)jdbcTemplate.queryForObject(sql.toString(), new Object[]{strCourseId}, new CourseSimpleMapper());
			KeyHolder keyHolder = new GeneratedKeyHolder();  
			final String sql1 = "insert into selected_course(student_id,student_name,course_id,courser_name,specialty_id,status,score,credit,apply_time ) VALUES (?,?,?,?,?,?,?,?,? )";
			jdbcTemplate.update(new PreparedStatementCreator() {  
			    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {  
				     PreparedStatement ps =con.prepareStatement(sql1,Statement.RETURN_GENERATED_KEYS);
				     
				     ps.setObject(1 , userId);
		    		 ps.setObject(2 ,userName);
				     ps.setObject(3 ,bean.getId());
				     ps.setObject(4 ,bean.getCourse_name());
				     ps.setObject(5 ,bean.getSpecialty_id());
				     if(null == bean.getPrices() || "0".equalsIgnoreCase(bean.getPrices()) || "0.0".equalsIgnoreCase(bean.getPrices()))
				    	 ps.setObject(6 ,1);
				     else 
				     {
				    	 double price = 0;
				    	 try{
				    		 price = Integer.valueOf(bean.getPrices());
				    	 }catch(Exception e){
				    	 }
				    	 ps.setObject(6 ,price > 0 ? 0 : 1 );
				     }
				     ps.setObject(7 ,0.0);
				     ps.setObject(8 ,bean.getCredit());
				     ps.setObject(9 ,CCPContants.sdf3.format(new Date()));
				     return ps;  
			    }  
			   }, keyHolder);  
			int selectCourseId =  keyHolder.getKey().intValue();
			String sql2 = "SELECT cd.*  FROM courses_detail cd WHERE cd.courses_id = ? AND cd.status != -1";
			List<CourseContentBean> couserList =  jdbcTemplate.query(sql2, new Object[]{strCourseId}, new CourseContentRowMapper());
			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("insert into selected_course_detail(selected_course_id,course_detail_id,chapter_number,chapter_title,section_number,section_file_name,section_file_type) VALUES  ");
			CourseContentBean b = null;
			if(couserList.size() >= 1){
				for(int i=0,n = couserList.size(); i<n; i++){
					b = couserList.get(i);
					sqlBuf.append("(");
					sqlBuf.append(selectCourseId);
					sqlBuf.append(",");
					sqlBuf.append(b.getId());
					sqlBuf.append(",");
					sqlBuf.append(b.getChapter_number());
					sqlBuf.append(",");
					sqlBuf.append("\""+b.getChapter_title()+"\"");
					sqlBuf.append(",");
					sqlBuf.append(b.getSection_number());
					sqlBuf.append(",");
					sqlBuf.append("'"+b.getSection_file_name()+"'");
					sqlBuf.append(",");
					sqlBuf.append("'"+b.getSection_file_type()+"'");
					if( i< n-1)
						sqlBuf.append("),");
					else
						sqlBuf.append(")");
				}
				log.info(sqlBuf);
				jdbcTemplate.update(sqlBuf.toString());
			}
	}
	
	
	
	/**
	 * 审批结果进行数据库操作
	 * @param status:审批专题
	 * @param selectCourseId：选课id
	 * @param approveTime：审批时间
	 * @return
	 */
	public boolean doApprove(int status,String selectCourseId,String approveTime){
		String sql = "update selected_course set status=?,approve_time = ? where id = ?";
		return jdbcTemplate.update(sql, new Object[]{status,approveTime,selectCourseId}) > 0;
	}
	
	/**
	 * 该节课程已经学习
	 * @param strSelectedCourseDetailId：学生选课详细内容id
	 * @param selectedCourseId：学生选课内容id
	 * @return
	 */
	public boolean doneStudy(final String courseDetailId,final String selectedCourseId){
		return (Boolean)transactionTemplate.execute(new TransactionCallback() {
			@Override
			public Object doInTransaction(TransactionStatus ts) {
				try{
					String sql = "SELECT count(1) FROM selected_course_detail where selected_course_id = ? and course_detail_id = ?";
					int count =  jdbcTemplate.queryForInt(sql, new Object[]{selectedCourseId,courseDetailId});
					if(count == 0){//没有记录，则插入新记录
						sql = "insert into selected_course_detail (selected_course_id,course_detail_id,has_studied) VALUES (?,?,true) "; 
						jdbcTemplate.update(sql, new Object[]{selectedCourseId,courseDetailId});
					}else if(count == 1){
						sql = "update  selected_course_detail set  has_studied = ? where  selected_course_id = ?  and course_detail_id = ? "; 
						jdbcTemplate.update(sql, new Object[]{true,selectedCourseId,courseDetailId});
					}
					sql = "update selected_course set last_study_time=? where id = ?";
					jdbcTemplate.update(sql, new Object[]{CCPContants.sdf3.format(new Date()),selectedCourseId});
					return Boolean.TRUE;
				}catch(Exception e){
					log.error("add select course failed : ",e);
					ts.setRollbackOnly();  
					return Boolean.FALSE; 
				}
			}
		});
	}
	
	public List<CourseContentBean> getCourseContentsList(String courseId,String selectCourseId){
		String sql = "SELECT cd.* ,c.courses_name,cdd.has_studied ,cdd.selected_course_id as selectCourseId FROM courses_detail cd "+
				" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+
				" LEFT OUTER JOIN selected_course_detail cdd ON cdd.course_detail_id = cd.id and cdd.selected_course_id = ? "+
				" WHERE cd.courses_id = ?   ORDER BY cd.chapter_number+0  ,cd.section_number+0,cd.other+0 asc";
	   Object[]	objs = new Object[]{selectCourseId,courseId};
	   log.info(sql);
	   log.info(selectCourseId +":" +courseId );
	   return jdbcTemplate.query(sql, objs, new CourseContentsRowMapper());
	} 
	
	
	
	
	/**
	 * 根据课程id查询该门课程的评价
	 * @param courseId
	 * @return
	 */
	public List<StudentMarkedHistoryBean> getMarkedList(String courseId){
		StringBuffer buf = new StringBuffer();
		buf.append("select sm.start_level,sm.marked_time,sm.student_marked_content,u.username ");
		buf.append(" from student_marked_history sm ");
		buf.append(" left outer join user u on u.id =sm.student_id ");
		buf.append(" where sm.course_id =  ? order by sm.marked_time ");
		return jdbcTemplate.query(buf.toString(), new Object[]{courseId}, new MarkedMapper());
	}
	
	
	/**
	 * 根据课程id查询该门课程的评价
	 * @param courseId
	 * @return
	 */
	public List<StudentMarkedHistoryBean> getMarkedList(String courseId,int start,int pageSize){
		StringBuffer buf = new StringBuffer();
		buf.append("select sm.start_level,sm.marked_time,sm.student_marked_content,u.username ");
		buf.append(" from student_marked_history sm ");
		buf.append(" left outer join user u on u.id =sm.student_id ");
		buf.append(" where sm.course_id =  ? order by sm.marked_time LIMIT ?,?");
		return jdbcTemplate.query(buf.toString(), new Object[]{courseId,start,pageSize}, new MarkedMapper());
	}
	
	
	
	class CourseContentDetailMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseContentBean bean = new CourseContentBean();
			bean.setId(rs.getInt("id"));
			bean.setChapter_number(rs.getString("chapter_number"));
			bean.setChapter_title(rs.getString("chapter_title"));
			bean.setSection_number(rs.getString("section_number"));
			bean.setSection_file_name(rs.getString("section_file_name"));
			bean.setSection_file_type(rs.getString("section_file_type"));
			bean.setSource_file_url(rs.getString("source_file_url"));
			bean.setSource_file_url(rs.getString("section_file_url"));
			bean.setCan_browse(rs.getString("can_browse"));
			return bean;
		}
	}
	
	
	
	class SelectCourseMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			SelectedCourseBean bean = new SelectedCourseBean();
			bean.setId(rs.getInt("id"));
			bean.setStudentId(rs.getInt("student_id"));
			bean.setStudentName(rs.getString("student_name"));
			bean.setCourseId(rs.getInt("course_id"));
			bean.setCourseName(rs.getString("courser_name"));
			bean.setSpecialtyId(rs.getInt("specialty_id"));
			bean.setStatus(rs.getInt("status"));
			bean.setScore(rs.getString("score"));
			bean.setCredit(rs.getString("credit"));
			bean.setHasMarked(rs.getBoolean("has_marked"));
			bean.setApproveTime(rs.getString("approve_time"));
			bean.setFirstStudyTime(rs.getString("first_study_time"));
			bean.setLastStudyTime(rs.getString("last_study_time"));
			bean.setApplyTime(rs.getString("apply_time"));
			return bean;
		}
	}
	
	/**
	 * 学生选课时查询返回对象
	 * @author renyongcheng
	 *
	 */
	class CourseMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseBean bean = new CourseBean();
			bean.setId(rs.getString("id"));
			bean.setCourse_name(rs.getString("courses_name"));
			bean.setStart_level(rs.getString("start_level"));
			bean.setImage(rs.getString("image"));
			bean.setTeacherId(rs.getString("teacher_id"));
			bean.setTeacherName(rs.getString("username"));
			bean.setMarks(rs.getInt("marks"));
			bean.setStudyUsers(rs.getInt("studys"));
			bean.setHasSelected(rs.getBoolean("studied"));
			bean.setFile_path_id(rs.getInt("file_path_id"));
			bean.setCourseResourse(rs.getString("originate"));
			bean.setCreate_time((""+rs.getString("create_time")).substring(0,10));
			return bean;
		}
	}
	
	class CourseDetailMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseBean bean = new CourseBean();
			bean.setId(rs.getString("id"));
			bean.setCourse_name(rs.getString("courses_name"));
			bean.setStart_level(rs.getString("start_level"));
			bean.setImage(rs.getString("image"));
			bean.setTeacherId(rs.getString("teacher_id"));
			bean.setTeacherName(rs.getString("username"));
			bean.setMarks(rs.getInt("marks"));
			bean.setStudyUsers(rs.getInt("studys"));
			bean.setSpecialty_name(rs.getString("specialtyname"));
			bean.setDescripte(rs.getString("descript"));
			bean.setTeacherDes(rs.getString("teacherDes"));
			bean.setIs_high_quality(rs.getString("is_high_quality"));
			log.info(rs.getString("is_high_quality"));
			log.info(bean.getIs_high_quality());
			if(bean.getIs_high_quality() == null)
				bean.setIs_high_quality("0");
			if(bean.getIs_high_quality().equals("0")){
				bean.setIs_high_quality_name(BaseAction.getInstance().getMessages("btn_no"));
			}else if(bean.getIs_high_quality().equals("1")){
				bean.setIs_high_quality_name(BaseAction.getInstance().getMessages("btn_yes"));
			}
			try {
				bean.setTeaching_group_name(rs.getString("teaching_group_name"));
			} catch (Exception e) {
				// TODO: handle exception
			}
			bean.setFile_path_id(rs.getInt("file_path_id"));
			return bean;
		}
	}
	
	class CourseSimpleMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseBean bean = new CourseBean();
			bean.setId(rs.getString("id"));
			bean.setCourse_name(rs.getString("courses_name"));
			bean.setSpecialty_id(rs.getString("specialty_id"));
			bean.setPrices(rs.getString("prices"));
			bean.setCredit(rs.getString("credit"));
			bean.setSpecialty_name(rs.getString("specialtyname"));
			return bean;
		}
	}
	
	
	
	class SpecialtyMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			SpecialtyBean rolesBean = new SpecialtyBean();
			rolesBean.setId(rs.getInt("id"));
			rolesBean.setSpecialtyname(rs.getString("specialtyname"));
			return rolesBean;
		}
	}
	
	class UserMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			UserBean user = new UserBean();
			user.setId(rs.getInt("id"));
			user.setUserName(rs.getString("username"));
			return user;
		}
	}
	
	 class CourseContentRowMapper implements RowMapper {
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				CourseContentBean bean = new CourseContentBean();
				bean.setId(rs.getInt("id"));
				bean.setTmpId(bean.getId());
				bean.setCourse_id(rs.getString("courses_id"));
				bean.setChapter_number(rs.getString("chapter_number"));
				bean.setChapter_title(rs.getString("chapter_title"));
				bean.setSection_number(rs.getString("section_number"));
				bean.setSection_file_name(rs.getString("section_file_name"));
				bean.setSection_file_type(rs.getString("section_file_type"));
				bean.setSource_file_url(rs.getString("source_file_url"));
				bean.setSection_file_url(rs.getString("section_file_url"));
				bean.setHas_training(rs.getString("has_training"));
				bean.setOther(rs.getString("other"));
				bean.setTotal_pages(rs.getString("total_pages"));
				bean.setCan_browse(rs.getString("can_browse"));
				return bean;
			}
		}
	
		class OnlineCourseMapper implements RowMapper {
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				SelectedCourse bean = new SelectedCourse();
				bean.setId(rs.getInt("id"));
				bean.setCourseId(rs.getInt("course_id"));
				bean.setCourserName(rs.getString("courser_name"));
				bean.setStarLevel(rs.getString("start_level"));
				bean.setTeacherName(rs.getString("username"));
				bean.setMarks(rs.getInt("marks"));
				bean.setFinishRate(rs.getDouble("studiedRate"));
				bean.setImage(rs.getString("image"));
				try {
					bean.setHasMarked(rs.getBoolean("has_marked"));
				} catch (Exception e) {
				}
				
				try {
					bean.setStatus(rs.getInt("status"));
				} catch (Exception e) {
				}
				bean.setFilePathId(rs.getInt("file_path_id"));
				
				return bean;
			}
		}
		
		class CourseSettingMapper implements RowMapper {
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				CourseSettingBean bean = new CourseSettingBean();
				bean.setId(rs.getString("id"));
				bean.setCourseId(rs.getString("course_id"));
				bean.setCourseName(rs.getString("courser_name"));
				bean.setTitle(rs.getString("title"));
				bean.setScore(rs.getString("score"));
				bean.setType(rs.getString("type"));
				log.info("----------"+bean.getType());
				int type = Integer.parseInt(bean.getType());
				try{
					switch(type){
						case 1:
							bean.setTypeName(BaseAction.getInstance().getMessages("course_page_course_test"));
							break;
						case 2:
							bean.setTypeName(BaseAction.getInstance().getMessages("course_page_course_training"));
							break;
						case 3:
							bean.setTypeName(BaseAction.getInstance().getMessages("course_page_course_survey_title"));
							break;
					}
				}catch(Exception e){}
				bean.setHasDone(rs.getString("recordid") == null ? false : true);
				try{
					bean.setIsDone(bean.isHasDone() ?  BaseAction.getInstance().getMessages("course_page_course_set_has_done") : BaseAction.getInstance().getMessages("course_page_course_set_has_no_done"));
				}catch(Exception e){}
				try{
					bean.setSelectedCourseId(rs.getString("selectCourseId"));
				}catch(Exception e){}
				return bean;
			}
		}
		
		
	 
		
		class MarkedMapper implements RowMapper {
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				StudentMarkedHistoryBean bean = new StudentMarkedHistoryBean();
				bean.setStarLevel(rs.getString("start_level"));
				bean.setStrStudentName(rs.getString("username"));
				bean.setMarkTime(rs.getString("marked_time"));
				bean.setContent(rs.getString("student_marked_content"));
				return bean;
			}
		}
		
		public class CourseContentsRowMapper implements RowMapper {
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				CourseContentBean bean = new CourseContentBean();
				bean.setId(rs.getInt("id"));
				bean.setTmpId(bean.getId());
				bean.setCourse_id(rs.getString("courses_id"));
//				CourseBean cbean = BaseAction.getInstance().getCourseService().getBeanById(bean.getCourse_id());
				bean.setCourse_name("");
				try {
					bean.setCourse_name(rs.getString("courses_name"));
				} catch (Exception e) {
				}
				bean.setChapter_number(rs.getString("chapter_number"));
				bean.setChapter_title(rs.getString("chapter_title"));
				bean.setSection_number(""+rs.getString("section_number"));
				bean.setSection_file_name(rs.getString("section_file_name"));
				bean.setSection_file_type(rs.getString("section_file_type"));
				bean.setSource_file_url(rs.getString("source_file_url"));
				bean.setSection_file_url(rs.getString("section_file_url"));
				bean.setHas_training(rs.getString("has_training"));
				bean.setOther(rs.getString("other"));
				bean.setTotal_pages(rs.getString("total_pages"));
				bean.setCan_browse(rs.getString("can_browse"));
				bean.setTotal_score(rs.getInt("total_score"));
				bean.setUpdateState(true);
				try {
					bean.setTraining_total_score(rs.getInt("training_total_score"));
				} catch (Exception e) {
					bean.setTraining_total_score(0);
				}
				bean.setFilePathId(rs.getInt("file_path_id")); 
				bean.setStatus(rs.getString("status"));
				
				bean.setSection_index(bean.getStatus());
				
				
				try {
					bean.setSelectCourseId(rs.getString("selectCourseId"));
					bean.setHaveStudty(rs.getBoolean("has_studied"));	
				} catch (Exception e) {
					// TODO: handle exception
				}
				
				return bean;
			}
		}
		
	 
}
